#coding=utf-8
from stockdb import *
from sqlalchemy.exceptions import InvalidRequestError

def histprices2db(fins, prices):
    
    pre = None
    pre_close = None
        
    for p in prices:
        
        #TradeDay
        td = TradeDay()
        
        td.fins = fins
        
        td.date = p.date
        if pre:td.pre = pre
        
        #Price
        price = Price()
        
        price.td = td
        price.open = p.open
        price.close = p.close
        price.highest = p.highest
        price.lowest = p.lowest
        
        price.volume = p.volume
        price.amount = p.amount
        
        if pre_close:
            price.change = (p.close - pre_close)/pre_close * 100
            price.jump = (p.open - pre_close)/pre_close * 100
            
        price.fluctuation = (p.highest - p.lowest) * 100 / p.close
        
        sess.save(td)
        sess.save(price)
        
        pre = td
        pre_close = p.close
        
        sess.commit()
            
class StockDBImporter(object):
    def __init__(self, code, source):
        self.code = code
        self.source = source
            
    def stock2db(self):
        s = self.source.get_stock(self.code)
        
        stk = Stock(s.name, s.code, s.market, s.ipo, s.iprice)
        #print stk.name, stk.iprice
        sess.save(stk)
        sess.commit()
        sess.close()
    
    def ts2db(self):
        while True:
            try:
                s = sess.query(Stock).filter_by(code=self.code).one()
                break
            except InvalidRequestError:
                self.stock2db()
        
        sts = self.source.get_ts(self.code)
        
        for t in sts.tss:
            ts = TradableShare()
            ts.stock = s
            ts.date = t[0]
            ts.quantity = t[1]
            
            sess.save(ts)
        
        sess.commit()
        sess.close()
        
    def histprices2db(self):
        while True:
            try:
                s = sess.query(Stock).filter_by(code=self.code).one()
                break
            except InvalidRequestError:
                self.stock2db()
        if not s.tss:
            self.ts2db()
            
        s = sess.query(Stock).filter_by(code=self.code).one()       
        ps = self.source.get_histprices(self.code)
        #print len(ps)

        pre = None
        if s.iprice : pre_close = s.iprice
        else:
            pre_close = None
            
        for p in ps:
            
            td = TradeDay()
            td.fins = s
            td.date = p.date
            if pre:td.pre = pre
            
            price = Price()
            
            price.td = td
            price.open = p.open
            price.close = p.close
            price.highest = p.highest
            price.lowest = p.lowest
            
            price.volume = p.volume
            price.amount = p.amount
            
            if pre_close:
                price.change = (p.close - pre_close)/pre_close * 100
                price.jump = (p.open - pre_close)/pre_close * 100
            ts = s.get_ts(p.date)
            price.trade_rate = p.volume * 100 / float(ts)
            price.fluctuation = (p.highest - p.lowest) * 100 / p.close
            
            sess.save(td)
            sess.save(price)
            
            pre = td
            pre_close = p.close
            
            sess.commit()
                
        sess.close()
        
if __name__ == '__main__':
    import sdsource.web.sina as sina
    from sdsource.web.stcode import get_shstcode
    
    cns = get_shstcode()
    codes = [cn[0] for cn in cns ]
    for c in codes:
        print c
        try:
            s = sess.query(Stock).filter_by(code=c).one()
        except InvalidRequestError:
            si = StockDBImporter(c, sina)
            si.stock2db()
            si.ts2db()
            si.histprices2db()
        else:
            if not s.tds:
                si = StockDBImporter(c, sina)
                si.histprices2db()
